package com.lovemanai.service;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;

import com.lovemanai.entity.City;
import com.lovemanai.entity.User;
import com.lovemanai.util.HibernateSessionFactory;

public class Demo {
	
	/*
	 * 外连接查询
	 */
	@Test
	public void test1() {
		
		Session session = HibernateSessionFactory.getSession();
		
		//Query query = session.createQuery(hql);
		//命名查询，降低java代码和页面的耦合
		Query query = session.getNamedQuery("myquery");
		
		List<City> list = query.list();
		for (City object : list) {
			System.out.println(object.getName());
		}
		if(session != null) session.close();
	}
	
	/*
	 * 动态实例化类
	 */
	@Test
	public void test2() {
		Session session = HibernateSessionFactory.getSession();
		//指定完整类名
		String hql = "select new com.lovemanai.entity.User(u.uname,u.city) from User u";
		
		Query query = session.createQuery(hql);
		List<User> users = query.list();//查询出来就是实体类，以后到页面迭代输出更容易
		for (User user : users) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if(session != null) session.close();
	}
	
	/*
	 * 本地sql
	 */
	@Test
	public void test3() {
		Session session = HibernateSessionFactory.getSession();
		
		SQLQuery sq = session.createSQLQuery("select * from user");//本地sql
		//使用addEntity()方法将对象数组变成对象
		sq = sq.addEntity(User.class);
		List<User> users = sq.list();
		for (User user : users) {
			System.out.println(user.getUname());
		}
//		List<Object[]> list = sq.list();//直接调用list方法，返回对象数组放入list数组中
//		for (Object[] object : list) {
//			System.out.println(object[2]);
//		}
//		
		if(session != null) session.close();
	}
	
	/*
	 * 在配置文件<sql-query></sql-query>标签中添加返回值标签<return/>指定返回值class
	 */
	@Test
	public void test4() {
		Session session = HibernateSessionFactory.getSession();
		
		Query query = session.getNamedQuery("mysqlquery");
		List<User> users = query.list();
		for (User user : users) {
			System.out.println(user.getUname());
		}
		if(session != null) session.close();
	}
	
	/*
	 * 本地sql执行dml语句
	 * 注意执行dml语句必须在事务中
	 */
	@Test
	public void test5() {
		Session session = HibernateSessionFactory.getSession();
		
		Transaction tx = session.beginTransaction();
		SQLQuery sq = session.createSQLQuery("update user set uname = 'Tom' where uid = 4");
		sq.executeUpdate();
		tx.commit();
		
		if(session != null) session.close();
	}
	
	/*
	 * Criteria查询
	 */
	@Test
	public void test6() {
		/*
		 * 1.创建Session
		 * 2.创建Criteria对象
		 * 3.调用list方法查询
		 */
		Session session = HibernateSessionFactory.getSession();
		
		Criteria c = session.createCriteria(User.class);
		List<User> list = c.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if(session != null) session.close();
	}
	
	/*
	 * Criteria查询
	 * 条件查询
	 */
	@Test
	public void test7() {
		
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		//添加查询条件
		c.add(Restrictions.and(Restrictions.ge("age", 23), Restrictions.ilike("uname", "%王%")));
		c.add(Restrictions.eq("city", session.get(City.class, 2)));//关联查询需要先从数据库中取出持久态对象
		List<User> list = c.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if(session != null) session.close();
	}
	
	/*
	 * Criteria查询
	 * 使用Example对象（QBE：Query By Example）
	 * 优势：使用实体进行查询
	 * 缺点：只能进行等值查询
	 * QBE查询会忽略对象中的null属性和关联属性
	 */
	@Test
	public void test8() {
		
		
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		
		User user = new User();
		user.setAge(23);
		City city = (City) session.get(City.class, 2);//关联查询还有些问题
		city.setName("西安");
		user.setCity(city);
		
		c.add(Example.create(user));
		List<User> list = c.list();
		for (User user1 : list) {
			System.out.println(user1.getUname() + "," + user1.getCity().getName());
		}
		if(session != null) session.close();
	}
	
	/*
	 * Criteria查询
	 * 排序和分页
	 */
	@Test
	public void test9() {
		
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(City.class);
		
		//c.addOrder(Order.asc("name"));//升序
		//c.addOrder(Order.desc("name"));//降序
		/*
		 * 分页
		 * 1.设置查询起始点
		 * 2.设置最大查询记录数
		 */
		c.setFirstResult(2);
		c.setMaxResults(3);
		
		List<City> citys = c.list();
		for (City city : citys) {
			System.out.println(city.getName());
		}
		if(session != null) session.close();
	}
	
	/*
	 * Criteria查询
	 * 聚合和分组
	 */
	@Test
	public void test10() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		/*
		c.setProjection(Projections.avg("age"));
		//c.setProjection(Projections.max("age"));//聚合查询只能设置一次，第二次的设置将会覆盖第一次
		System.out.println(c.uniqueResult());
		*/
		
/*
		//一次查询多个聚合函数
		//
		// 1.创建一个ProjectionList对象
		// 2.将多个聚合函数放入List中
		//
		ProjectionList plist = Projections.projectionList();
		plist.add(Projections.avg("age"));
		plist.add(Projections.max("age"));
		c.setProjection(plist);
		List<Object[]> list = c.list();
		for (Object[] objects : list) {
			System.out.println("avg(age):" + objects[0] + "," + "max(age):" + objects[1] );
		}
*/
		//聚合和分组
		
		ProjectionList plist = Projections.projectionList();
		plist.add(Projections.avg("age"));
		plist.add(Projections.groupProperty("city"));
		c.setProjection(plist);
		List<Object[]> list = c.list();
		for (Object[] objects : list) {
			System.out.println("avg(age):" + objects[0] );
		}
	}
}

